<?php

namespace App\Services\Stats\User;

use App\Models\Game\Log\UserLogin;
use App\Models\Game\Settlement\Info;
use App\Models\Game\User\User;
use App\Services\Service;
use App\Services\Traits\DatabaseMysql;
use App\Models\Admin\Stats\Retention\Day\Game;
use App\Models\Admin\Stats\Retention\Day\Sign;
use Helper\Timed;

class RetentionService extends Service
{
	use DatabaseMysql;

	/**
	 * 制作统计字段
	 *
	 * @return string
	 */
	protected function statsFields()
	{
		$columns[] = 'timed';
		$columns[] = 'SUM(drr_1_num) as drr_1_num';
		$columns[] = 'SUM(drr_2_num) as drr_2_num';
		$columns[] = 'SUM(drr_3_num) as drr_3_num';
		$columns[] = 'SUM(drr_7_num) as drr_7_num';
		$columns[] = 'SUM(drr_15_num) as drr_15_num';
		$columns[] = 'SUM(drr_30_num) as drr_30_num';

		return implode(',', $columns);
	}

	/**
	 * 留存统计天数
	 *
	 * @var int[]
	 */
	protected $days = [1, 2, 3, 7, 15, 30];

	/**
	 * 数据列表【游戏留存】
	 */
	public function game($request)
	{
		$query = Info::query();

		$query->where('status', Info::STATUS_NORMAL)->where('player_type', Info::PLAYER_TYPE);
		// 筛选【时间】
		$this->makeWhereParamDatetimeTimeZone($request, $query, 'end_time');

		// 筛选【平台】
		$this->makeWhereParamOrg($request, $query);

		// 排序
		$this->makeOrderParam($request, $query);

		//分页
		$this->makePaginationParam($request, $query);
		// 获取数据
		list($collection, $total) = RetentionService::create()->getGameData($request, $query, 'timed');

		return [
			$collection,
			$total
		];
	}

	/**
	 * 数据列表【注册留存】
	 */
	public function sign($request)
	{
		$query = User::query();

		// 筛选【平台】
		$this->makeWhereParamOrg($request, $query);

		// 筛选【时间】
		$this->makeWhereParamDatetimeTimeZone($request, $query, 'registration_time');

		// 排序
		$this->makeOrderParam($request, $query);

		//分页
		$this->makePaginationParam($request, $query);
		// 获取数据
		list($collection, $total) = RetentionService::make()->getSignData($request, $query, 'timed');

		return [
			$collection,
			$total
		];
	}

	/**
	 * 获取用户数据和总条数
	 *
	 * @param \Illuminate\Database\Eloquent\Builder $query
	 * @return array
	 */
	private function getSignData($request, $query, $groupBy)
	{
		//转换指定时区
		$timezone = Timed::getTimeCode();
		$columns[] ="DATE(CONVERT_TZ(DATE_FORMAT(FROM_UNIXTIME(registration_time), '%Y-%m-%d %H:%i:%s'), '+08:00', '".(preg_replace("/^UTC/", "", $timezone))."')) as timed";
		$columns[] = 'COUNT(*) as num';
		$columns[] = 'min(id) as id_min';
		$columns[] = 'max(id) as id_max';

		$query->groupByRaw($groupBy);

		return User::dbConnection()->transaction(function () use ($request, $query, $columns) {
			// 查询数据
			$collection = $query->getQuery()
				->selectRaw('SQL_CALC_FOUND_ROWS ' . implode(',', $columns))
				->orderBy('timed', 'desc')
				->get();

			// 数据条数
			$total = (int)User::dbConnection()->select("SELECT FOUND_ROWS() as `row_count`")[0]['row_count'];

			$collection->transform(function ($item)use($request) {
				date_default_timezone_set(Timed::getTimezoneName());

				$item['timed_str'] = $item['timed'];
				$item['timed'] = strtotime($item['timed']);

				$stat = [];
				foreach ($this->days as $day) {
					$timedDay = $item['timed'] + ($day - 1) * 86400;
					//指定日期注册用户数量
					$ids = [
						'id_min' => $item['id_min'],
						'id_max' => $item['id_max'],
					];
					//当日登录用户数量
					$stat["drr_{$day}_num"] = is_null($ids) ? 0 : ($day == 1 ? $item['num'] : RetentionService::create()->getSignUserCount($timedDay, $ids, $request));
				}
				return $item + $stat +
					[
						'time_zone' => Timed::getTimezoneName(),
					];
			});
			$collection = $collection->toArray();
			$collection[0]['drr_2_num'] = 0;

			return [$collection, $total];
		});
	}


	/**
	 * 获取游戏数据和总条数
	 *
	 * @param \Illuminate\Database\Eloquent\Builder $query
	 * @return array
	 */
	private function getGameData($request, $query, $groupBy)
	{
		//转换指定时区
		date_default_timezone_set(Timed::getTimezoneName());
		// $timezone = $request->user()->config->timezoneArr->code;
		$columns[] ="DATE(CONVERT_TZ(DATE_FORMAT(FROM_UNIXTIME(end_time), '%Y-%m-%d %H:%i:%s'), '+08:00', '". Timed::getTimeCode() ."')) as timed";
		$columns[] = 'GROUP_CONCAT(DISTINCT uid ORDER BY uid) AS unique_uids';

		$query->groupByRaw($groupBy);

		return User::dbConnection()->transaction(function () use ($request, $query, $columns) {
			// 查询数据
			$collection = $query->getQuery()
				->selectRaw('SQL_CALC_FOUND_ROWS ' . implode(',', $columns))
				->orderBy('timed', 'desc')
				->get();

			// 数据条数
			$total = (int)User::dbConnection()->select("SELECT FOUND_ROWS() as `row_count`")[0]['row_count'];

			$collection->transform(function ($item)use($request) {
				$item['timed'] = strtotime($item['timed']);
				$ids = explode(',', $item['unique_uids']);
				$stat = [];
				foreach ($this->days as $day) {
					$timedDay = $item['timed'] + ($day - 1) * 86400;

					//当日登录用户数量
					$stat["drr_{$day}_num"] = is_null($ids) ? 0 : $this->getGameUserCount($timedDay, $ids);
				}
				return $item + $stat +
					[
						'time_zone' => Timed::getTimezoneName(),
					];
			});

			return [$collection, $total];
		});
	}

	/**
	 * 获取玩家人数
	 *
	 * @param int $timed
	 * @param string $platCode
	 * @param array $ids
	 * @return int
	 */
	private function getSignUserCount(int $timed, array $ids, $request): int
	{
		$query = UserLogin::dbTable()
			->where('uid', '>=', $ids['id_min'])
			->where('uid', '<=', $ids['id_max'])
			->where('login_time', '>=', $timed);

		// 筛选【平台】
		$this->makeWhereParamOrg($request, $query);

		return $query->distinct('uid')->count();
	}

	/**
	 * 获取游戏人数
	 *
	 * @param int $timed
	 * @param array $ids
	 * @return int
	 */
	private function getGameUserCount(int $timed, array $ids): int
	{
		return Info::dbTable()->whereIn('uid', $ids)
			->where('end_time', '>=', $timed)
			->distinct('uid')
			->count();
	}
}
